#!/usr/bin/env python
# -*- coding:utf-8 -*-

""" 
@author: zhaoqifa@baidu.com
@license: Copyright (c) 2013 Baidu.com, Inc. All Rights Reserved
"""
import os
import types
from lib.third.mysql import MySQLdb

def add_columns(db_con,table,column_list):
    if len(column_list) == 0:
        return 0
    col_add_string = ""
    for column_name,column_type in column_list:
        if column_type == types.IntType or column_type == types.LongType:
            db_type = "BIGINT"
        elif column_type == types.FloatType:
            db_type = "FLOAT"
        else:
            db_type = "VARCHAR(50)"
        col_add_string = "%s, ADD `%s` %s NOT NULL"%(col_add_string,column_name,db_type)
    col_add_string = col_add_string.strip(", ")
    cmd_add_column = "ALTER TABLE `%s` %s"%(table,col_add_string)
    db_con.query(cmd_add_column)
    return len(column_list)

def add_columns_v2(db_con,table,column_list):
    if len(column_list) == 0:
        return 0
    col_add_string = ""
    for column_name,column_type in column_list:
        if column_type == 'INT' or column_type == 'BIGINT':
            col_add_string = "%s, ADD `%s` %s DEFAULT 0 "%(col_add_string,column_name,column_type)
        else:
            col_add_string = "%s, ADD `%s` %s "%(col_add_string,column_name,column_type)
    col_add_string = col_add_string.strip(", ")
    cmd_add_column = "ALTER TABLE `%s` %s"%(table,col_add_string)
    db_con.query(cmd_add_column)
    return len(column_list)

def drop_colums(db_con,table,column_list):
    if len(column_list) == 0:
        return 0
    col_drop_string = ""
    for col in column_list:
        col_drop_string = "%s, DROP `%s`"%(col_drop_string,col)
    col_drop_string = col_drop_string.strip(", ")
    cmd_drop_column = "ALTER TABLE `%s` %s"%(table,col_drop_string)
    db_con.query(cmd_drop_column)
    return 0

def get_column_list(db_con,table,get_type=False):
    desc_cmd = "desc %s"%(table)
    db_con.query(desc_cmd)
    desc_result = db_con.store_result()
    row = desc_result.fetch_row()
    column_list = []
    while len(row) > 0:
        if get_type:
            column_list.append((row[0][0],row[0][1]))
        else:
            column_list.append(row[0][0])
        row = desc_result.fetch_row()
    return column_list

def add_columns_if_not_exist(db_con,table,column_type_value_list):
    existing_columns = get_column_list(db_con,table)
    new_column_list = []
    for cname,ctype,cvalue in column_type_value_list:
        if cname not in existing_columns:
            new_column_list.append((cname,ctype))
    return add_columns(db_con,table,new_column_list)
    
def add_columns_if_not_exist_v2(db_con,table,column_type_list):
    existing_columns = get_column_list(db_con,table)
    new_column_list = []
    for cname,ctype in column_type_list:
        if cname not in existing_columns:
            new_column_list.append((cname,ctype))
    return add_columns_v2(db_con,table,new_column_list)

def create_table_if_not_exists(db_con,table):
    cmd_create_table = "CREATE TABLE IF NOT EXISTS `%s` ( host VARCHAR(50) NOT NULL,\
                    time DATETIME NOT NULL, PRIMARY KEY (`host`,`time`), key `time_host30`(`time`,`host`(30)) );"%(table)
    db_con.query(cmd_create_table)
    cmd_drop = "drop procedure if exists %s_autoclean;"%table
    db_con.query(cmd_drop)
    cmd_proc = "CREATE PROCEDURE %s_autoclean() BEGIN DELETE FROM %s WHERE time < FROM_UNIXTIME(UNIX_TIMESTAMP(CURRENT_TIMESTAMP)-(10*24*3600)); END" % (table,table)
    db_con.query(cmd_proc)
    cmd_event = "CREATE EVENT IF NOT EXISTS %s_autoclean_event ON SCHEDULE EVERY 30 MINUTE DO CALL %s_autoclean();" % (table,table)
    db_con.query(cmd_event)

def create_table_if_not_exists_v2(db_con,table,grouptag):
    cmd_create_table = "CREATE TABLE IF NOT EXISTS `%s` (host VARCHAR(50) NOT NULL,\
                        time DATETIME NOT NULL, `%s` VARCHAR(50) NOT NULL, PRIMARY KEY(\
                        `host`,`time`,`%s`), key `time_host_tag`(`time`,`host`(30),`%s`))"%(table,grouptag,grouptag,grouptag)
    db_con.query(cmd_create_table)
    '''
    cmd_drop = "drop procedure if exists %s_autoclean;"%table
    db_con.query(cmd_drop)
    cmd_proc = "CREATE PROCEDURE %s_autoclean() BEGIN DELETE FROM %s WHERE time < FROM_UNIXTIME(UNIX_TIMESTAMP(CURRENT_TIMESTAMP)-(10*24*3600)); END" % (table,table)
    db_con.query(cmd_proc)
    cmd_event = "CREATE EVENT IF NOT EXISTS %s_autoclean_event ON SCHEDULE EVERY 30 MINUTE DO CALL %s_autoclean();" % (table,table)
    db_con.query(cmd_event)
    '''

def truncate_table(db_con,table):
    cmd_truncate_table = "truncate table `%s`;"%(table)
    db_con.query(cmd_truncate_table)

def get_max_value(db_con,table,column):
    cmd_max_value = "select max(%s) from %s;"%(column,table)
    db_con.query(cmd_max_value)
    max_value = db_con.store_result().fetch_row()[0][0]
    return max_value

def aggregate_data(db_con,table,aggre_op_list=("avg",)):
    for aggre_op in aggre_op_list:
        aggregate_table(db_con,table,aggre_op)

def aggregate_table(db_con,table,aggre_op="avg"):
    if aggre_op == "avg":
        target_table = "%s_5m"%(table)
    else:
        target_table = "%s_5m_%s"%(table,aggre_op)
    temp_table = "%s_tmp"%(table)
    create_table_if_not_exists(db_con,target_table)
    create_table_if_not_exists(db_con,temp_table)
    columns_string = "`host`,`time`"
    aggre_string = "src.host,FROM_UNIXTIME(UNIX_TIMESTAMP(src.time)-UNIX_TIMESTAMP(src.time)%(5*60)) as timekey"
    assign_string = ""
    item_type_list = get_column_list(db_con,table,True)
    aggre_item_type_list = []
    for i,t in item_type_list:
        if i == "host" or i == "time":
            continue
        aggre_i = i
        aggre_item_type_list.append((aggre_i,t))
        
        aggre_string = "%s,%s(src.`%s`)"%(aggre_string,aggre_op,i)
        assign_string = "%s,d.`%s`=s.`%s`"%(assign_string,aggre_i,aggre_i)
        columns_string = "%s,`%s`"%(columns_string,aggre_i)
    assign_string = assign_string.strip(",")

    add_columns_if_not_exist_v2(db_con,target_table,aggre_item_type_list)
    add_columns_if_not_exist_v2(db_con,temp_table,aggre_item_type_list)
    
    truncate_table(db_con,temp_table)
    last_time = get_max_value(db_con,target_table,"time")
    cmd = "insert into %s (%s) select %s from %s as src"%(temp_table,columns_string,aggre_string,table)
    if last_time is not None:
        cmd = "%s where src.time>='%s'"%(cmd,last_time)
    cmd = "%s group by src.host,timekey;"%(cmd)
    db_con.query(cmd)
    
    cmd = "update %s as d, %s as s set %s where d.time=s.time and d.host=s.host;"%(target_table,temp_table,assign_string)
    db_con.query(cmd)

    cmd = "insert into %s (%s) select %s from %s as s where not exists(select 1 from %s as d where d.host = s.host and d.time=s.time);"%(target_table,columns_string,columns_string,temp_table,target_table)
    db_con.query(cmd)
    db_con.commit()

def update_aggregate_data_proc(db_con,table,aggre_op="avg"):
    if aggre_op == "avg":
        target_table = "%s_5m"%(table)
    else:
        target_table = "%s_5m_%s"%(table,aggre_op)
    temp_table = "%s_tmp"%(table)
    create_table_if_not_exists(db_con,target_table)
    create_table_if_not_exists(db_con,temp_table)
    columns_string = "host,time"
    aggre_string = "src.host,FROM_UNIXTIME(UNIX_TIMESTAMP(src.time)-UNIX_TIMESTAMP(src.time)%(5*60)) as timekey"
    assign_string = ""
    item_type_list = get_column_list(db_con,table,True)
    aggre_item_type_list = []
    for i,t in item_type_list:
        if i == "host" or i == "time":
            continue
        aggre_i = i
        aggre_item_type_list.append((aggre_i,t))
        
        aggre_string = "%s,%s(src.%s)"%(aggre_string,aggre_op,i)
        assign_string = "%s,d.%s=s.%s"%(assign_string,aggre_i,aggre_i)
        columns_string = "%s,%s"%(columns_string,aggre_i)
    assign_string = assign_string.strip(",")

    add_columns_if_not_exist_v2(db_con,target_table,aggre_item_type_list)
    add_columns_if_not_exist_v2(db_con,temp_table,aggre_item_type_list)

    aggre_proc_name = "%s_aggr_proc"%(table)
    cmd = "drop procedure if exists %s"%(aggre_proc_name)
    db_con.query(cmd)
    cmd_list = []
    cmd_list.append("create procedure %()"%(aggre_proc_name))
    cmd_list.append("BEGIN")
    cmd_list.append("create table if not exists %s like %s;"%(temp_table,table))
    cmd_list.append("truncate table %s;"%(temp_table))
    cmd_list.append("create table if not exists %s like %s;"%(target_table,table))
    cmd_list.append("insert into %s (%s) select %s from %s as src where time>=(select max(time) from %s);"%(temp_table,columns_string,aggre_string,table,target_table))
    cmd_list.append("update %s as d, %s as s set %s where d.time=s.time and d.host=s.host;"%(target_table,temp_table,assign_string))
    cmd_list.append("insert into %s (%s) select %s from %s as s where not exists(select 1 from %s as d where d.host = s.host and d.time=s.time);"%(target_table,columns_string,columns_string,temp_table,target_table))
    cmd_list.append("END")
    return 0

